using System;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Security.Principal;
using System.DirectoryServices;

public partial class AddInstance : System.Web.UI.Page
{
    Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase("Application_Data");

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            if (Convert.ToBoolean(Session["sAdministrator"].ToString()) == false)
            {
                Response.Redirect("~/AccessDenied.aspx");
            }           
            
            div2.Visible = false;
        }
    }
    protected void btnAddInstance_Click(object sender, EventArgs e)
    {
        try
        {
            DbCommand dbCommand2 = db.GetSqlStringCommand("INSERT INTO Instance (InstanceName,InstanceShortDescription,PrimaryContactEMail) VALUES(" +
                                                     "'" + this.lblInstanceName.Text.ToUpper() + "'," +
                                                     "'" + this.txtShortDescription.Text.ToString() + "'," +
                                                     "'" + this.txtInstanceContactEMail.Text.ToString() + "')");


            db.ExecuteNonQuery(dbCommand2);

            //Load Repository to get initial load of data
            Utility.LoadRepository(this.txtInstanceName.Text.ToUpper());

            String strAlertAddressesAvailable = db.ExecuteScalar(CommandType.Text, "SELECT COUNT(*) FROM [DBAContact] WHERE [DBAOnCallFlag] = 1 or [DBANotificationFlag] = 1 ").ToString();

            if (strAlertAddressesAvailable == "0")
            {
                Response.Redirect(@"~/InstanceDetail.aspx?InstanceName=" + this.lblInstanceName.Text.ToString());
            }
            else
            {
                Utility.DBANotificationAlert(@"New Instance: " + this.lblInstanceName.Text.ToString() + " Has been added to EnterpriseDBA.", this.txtInstanceName.Text.ToString(), "Medium");
                Response.Redirect(@"~/InstanceDetail.aspx?InstanceName=" + this.lblInstanceName.Text.ToString());
            }
        }
        catch (Exception err)
        {
            MessageBox.Show(err.Message.ToString());
        }       
    }
    protected void btnVerifyInstance_Click(object sender, EventArgs e)
    {
        string sqlQuery = "if exists(SELECT InstanceName FROM Instance WHERE InstanceNAme = '" + this.txtInstanceName.Text.ToString() + "') SELECT 'true' else SELECT 'false'";
        string retValue = db.ExecuteScalar(CommandType.Text, sqlQuery).ToString();

        if (retValue == "false")
        {
            string MachineName = String.Empty;
            string strInstanceName = String.Empty;
            string SQLServerServiceName = String.Empty;
            
            strInstanceName = this.txtInstanceName.Text.ToString();

            if (strInstanceName.Contains(@"\"))
            {
                Int32 intLocation = strInstanceName.IndexOf(@"\");
                MachineName = strInstanceName.ToUpper().Substring(0, intLocation);
                SQLServerServiceName = "MSSQL$" + strInstanceName.Substring(intLocation + 1);                
            }
            else
            {
                MachineName = strInstanceName.ToString().ToUpper();
                SQLServerServiceName = "MSSQLSERVER";                
            }

            try
            {
                //CHECK CONNECTIVITY
                SqlConnection conn = new SqlConnection(@"server=" + strInstanceName + ";database=master;Integrated Security=SSPI;Pooling=False");
                conn.Open();

                SqlDataAdapter a1 = new SqlDataAdapter("SELECT CONVERT(int, SUBSTRING(CONVERT(varchar(15), SERVERPROPERTY('productversion')), 0, CHARINDEX('.', CONVERT(varchar(15), SERVERPROPERTY('productversion')))))", conn);
                DataSet s1 = new DataSet();
                a1.Fill(s1);
                DataRow dr1 = s1.Tables[0].Rows[0];


                if (Convert.ToInt32(dr1[0].ToString()) > 8)
                {
                    SqlCommand cmdXPCmdShell_1 = new SqlCommand();
                    SqlCommand cmdXPCmdShell_2 = new SqlCommand();
                    SqlCommand cmdXPCmdShell_3 = new SqlCommand();
                    SqlCommand cmdXPCmdShell_4 = new SqlCommand();

                    cmdXPCmdShell_1.Connection = conn;

                    cmdXPCmdShell_1.CommandText = "EXEC sp_configure 'show advanced options', 1";
                    cmdXPCmdShell_1.ExecuteNonQuery();

                    cmdXPCmdShell_2.Connection = conn;
                    cmdXPCmdShell_2.CommandText = "RECONFIGURE WITH OVERRIDE";
                    cmdXPCmdShell_2.ExecuteNonQuery();

                    cmdXPCmdShell_3.Connection = conn;
                    cmdXPCmdShell_3.CommandText = "EXEC sp_configure 'xp_cmdshell', 1";
                    cmdXPCmdShell_3.ExecuteNonQuery();

                    cmdXPCmdShell_4.Connection = conn;
                    cmdXPCmdShell_4.CommandText = "RECONFIGURE WITH OVERRIDE";
                    cmdXPCmdShell_4.ExecuteNonQuery();

                    conn.Close();

                    this.btnVerifyInstance.Visible = false;
                    this.lblInstanceName.Text = this.txtInstanceName.Text.ToUpper();
                    this.txtShortDescription.Enabled = true;
                    this.btnAddInstance.Enabled = true;

                    this.div2.Visible = true;
                }
                else
                {
                    MessageBox.Show("SQL Server Version Not Supported.  Only SQL Server versions greater than SQL Server 2005 supported.");
                    return;
                }               
            }
            catch (Exception err1)
            {
                MessageBox.Show("Error: " + err1.Message.ToString());
                return;
            }            
        }
        else
        {
            this.txtInstanceName.Text = this.txtInstanceName.Text.ToUpper();

            MessageBox.Show(this.txtInstanceName.Text.ToUpper() + " already exists in the repository.");
            return;            
        }
    }
}